/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import dto.CauHoiDTO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Stanley
 */
public class CauHoiDAO {

    public ArrayList getAllQuesttion() {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            ResultSet rs = query.executeQuery("select * from cauhoi");
            ArrayList dsCauhoi = new ArrayList();
            while (rs.next()) {
                dsCauhoi.add(
                        new CauHoiDTO(
                        rs.getInt("maCH"),
                        rs.getString("noidung"),
                        rs.getString("luachon1"),
                        rs.getString("luachon2"),
                        rs.getString("luachon3"),
                        rs.getString("luachon4"),
                        rs.getInt("dapan"),
                        rs.getInt("dokho")));
            }

            return dsCauhoi;
        } catch (Exception e) {
            return null;
        }
    }

    public boolean insertQuestion(CauHoiDTO info) {
        Connection connection = SqlDatabase.getInstance();
        try {
            CallableStatement query = connection.prepareCall("insert into cauhoi (noidung, luachon1, luachon2, luachon3, luachon4, dapan, dokho) values (?, ?, ?, ?, ?, ?, ?)");
            // set params
            query.setString(1, info.getNoidung());
            query.setString(2, info.getLuachon1());
            query.setString(3, info.getLuachon2());
            query.setString(4, info.getLuachon3());
            query.setString(5, info.getLuachon4());
            query.setInt(6, info.getDapan());
            query.setInt(7, info.getDokho());
            int count = query.executeUpdate();

            return count == 0 ? false : true;
        } catch (Exception e) {
            return false;
        }
    }

    public int getSoLuongCauHoi(String condition) {
        //query2.executeQuery("select count(*) as soluong from cauhoi where dokho = 1");
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select count(*) as soluong from cauhoi where " + condition;
            ResultSet rs = query.executeQuery(sql);
            rs.next();
            return rs.getInt("soluong");
        } catch (Exception e) {
            return -1;
        }
    }

    public ArrayList getDSCauHoi(String condition, int limit) {
        //query2.executeQuery("select * from cauhoi where dokho = 1 LIMIT " + Integer.toString(random) + " , 20");
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select * from cauhoi where " + condition + " LIMIT " + Integer.toString(limit) + " , 20";
            ResultSet rs = query.executeQuery(sql);
            ArrayList dsCauhoi = new ArrayList();
            while (rs.next()) {
                dsCauhoi.add(
                        new CauHoiDTO(
                        rs.getInt("maCH"),
                        rs.getString("noidung"),
                        rs.getString("luachon1"),
                        rs.getString("luachon2"),
                        rs.getString("luachon3"),
                        rs.getString("luachon4"),
                        rs.getInt("dapan"),
                        rs.getInt("dokho")));
            }

            return dsCauhoi;
        } catch (Exception e) {
            return null;
        }
    }
}
